#  -*- coding: utf-8 -*-
import json
from decimal import Decimal
from sqlalchemy import create_engine
from setting import Setting
from pgservice.pg_goto_mysql import Pg_to_mysql
import datetime as dt
import pymysql




class DBService(object):   # 对数据库的操作


    db_pool = None
    seting = Setting()      # 初始化配置类对象
    pg_conn = Pg_to_mysql()   # 连接pg数据库

    def __init__(self):
        self.conn = DBService.conn(self.seting.MYSQL_USER,self.seting.MYSQL_PWD,self.seting.MYSQL_HOST,self.seting.MYSQL_PORT,self.seting.MYSQL_DB,self.seting.MYSQL_CHAR)

    # 静态方法
    @staticmethod
    def conn(MYSQL_USER,MYSQL_PWD,MYSQL_HOST,MYSQL_PORT,MYSQL_DB,MYSQL_CHAR):
        if DBService.db_pool is None:
            user = MYSQL_USER
            pwd = MYSQL_PWD
            host = MYSQL_HOST
            port = MYSQL_PORT
            dbName = MYSQL_DB
            charset = MYSQL_CHAR
            url = "mysql+pymysql://"+user+":"+pwd+"@"+host+":"+port+"/"+dbName+"?charset="+charset
            DBService.db_pool = create_engine(url)
        return DBService.db_pool

    # 自定义json格式序列化
    def default(self,obj):
        if isinstance(obj, Decimal):
            return str(obj)
        raise TypeError("Object of type '%s' is not JSON serializable" % type(obj).__name__)

    # 遍历数据库查询 整合降峰推荐需要的json数据
    def DataToJson(self,tgNo, startDate, endDate):
        try:
            #  t10 用来获得台区三相表    sql 理解  获取台区先单项表用户  在这段时间内 每相电流每个小时内的平均值
            sql = '''select  t6.meterAssetNo,t6.phasE,ctRatio*avg(t6.i1),t6.ctRatio*avg(t6.i5),t6.ctRatio*avg(t6.i9),
            t6.ctRatio*avg(t6.i13),t6.ctRatio*avg(t6.i17),t6.ctRatio*avg(t6.i21),t6.ctRatio*avg(t6.i25),t6.ctRatio*avg(
            t6.i29),t6.ctRatio*avg(t6.i33),t6.ctRatio*avg(t6.i37),t6.ctRatio*avg(t6.i41),t6.ctRatio*avg(t6.i45),
            t6.ctRatio*avg(t6.i49),t6.ctRatio*avg(t6.i53),t6.ctRatio*avg(t6.i57),t6.ctRatio*avg(t6.i61),t6.ctRatio*avg(
            t6.i65),t6.ctRatio*avg(t6.i69),t6.ctRatio*avg(t6.i73),t6.ctRatio*avg(t6.i77),t6.ctRatio*avg(t6.i81),
            t6.ctRatio*avg(t6.i85),t6.ctRatio*avg(t6.i89),t6.ctRatio*avg(t6.i93)
            from ( 
                select t4.*,t3.tgNo,t3.ctRatio,t3.phasE from 
                    (
                        SELECT t2.assetNo as assetNo1,t2.ctRatio as ctRatio,t2.phase,t1.* FROM cp t1 LEFT JOIN meter t2 ON t1.tgNo = 
                        t2.tgNo WHERE t2.isParent = 0  and t2.tgNo = %s  and t2.phase!=-1 and t2.phase!=0
                    ) t3  
                left join dl_pdjk t4 
                on  t3.assetNo1= t4.meterAssetNo
                where t4.phaseFlag in (1,2,3)
                ) as t6 
                WHERE dataDate>= %s and dataDate< %s and  t6.meterAssetNo not in(select t11.meterAssetNo from 
            (select 
            case a when 1 THEN case b when 2 THEN case c when 3 then meterAssetNo end end end as meterAssetNo
            from (
            select 
            max(case t10.phase WHEN 1 then 1 end) as a, 
            max(case t10.phase WHEN 2 then 2 end) as  b, 
            max(case t10.phase WHEN 3 then 3 end) as c,
            t10.meterAssetNo
            from (select t6.dataDate,t6.meterAssetNo,t6.phase from (select t4.*,t3.tgNo,t3.ctRatio,t3.phase from 
                    (
                        SELECT t2.assetNo as assetNo1,t2.ctRatio as ctRatio,t2.phase,t1.* FROM cp t1 LEFT JOIN meter t2 ON t1.tgNo = 
                        t2.tgNo WHERE t2.isParent = 0  and t2.tgNo =  %s  and t2.phase!=-1 and t2.phase!=0
                    ) t3  
                left join dl_pdjk t4 
                on  t3.assetNo1= t4.meterAssetNo
                where t4.phaseFlag in (1,2,3) )as t6 WHERE dataDate>= %s and dataDate< %s ) as t10  GROUP  BY t10.dataDate ,t10.meterAssetNo) t12 ) t11 where t11.meterAssetNo  IS NOT NULL)  GROUP BY t6.meterAssetNo,t6.phasE '''

            # 4.执行sql命令
            res = self.conn.execute(sql, (tgNo, startDate, endDate, tgNo, startDate, endDate))
            # 返回的数据类型是元组类型，每个条数据元素为元组类型:(('第一条数据的字段1的值','第一条数据的字段2的值',...,'第一条数据的字段N的值'),(第二条数据),...,(第N条数据))
            data = res.fetchall()
            f = {}
            # 循环读取元组数据  {"A"：{"meterAssetNo":[1,2,3,4,5,6,7]}}
            # 将元组数据转换为列表类型，每个条数据元素为字典类型:[{'字段1':'字段1的值','字段2':'字段2的值',...,'字段N:字段N的值'},{第二条数据},...,{第N条数据}]
            # A相数据
            da = {}
            # B相数据
            db = {}
            # C相数据
            dc = {}

            for row in data:
                # 每一条A相数据的临时存放
                resulta = []
                # 每一条B相数据的临时存放
                resultb = []
                # 每一条C相数据的临时存放
                resultc = []

                if row[1] == 1:  # A相用户
                    resulta.append(row[2])
                    resulta.append(row[3])
                    resulta.append(row[4])
                    resulta.append(row[5])
                    resulta.append(row[6])
                    resulta.append(row[7])
                    resulta.append(row[8])
                    resulta.append(row[9])
                    resulta.append(row[10])
                    resulta.append(row[11])
                    resulta.append(row[12])
                    resulta.append(row[13])
                    resulta.append(row[14])
                    resulta.append(row[15])
                    resulta.append(row[16])
                    resulta.append(row[17])
                    resulta.append(row[18])
                    resulta.append(row[19])
                    resulta.append(row[20])
                    resulta.append(row[21])
                    resulta.append(row[22])
                    resulta.append(row[23])
                    resulta.append(row[24])
                    resulta.append(row[25])
                    da[row[0]] = resulta
                    f['A'] = da

                if row[1] == 2:  # B相用户
                    resultb.append(row[2])
                    resultb.append(row[3])
                    resultb.append(row[4])
                    resultb.append(row[5])
                    resultb.append(row[6])
                    resultb.append(row[7])
                    resultb.append(row[8])
                    resultb.append(row[9])
                    resultb.append(row[10])
                    resultb.append(row[11])
                    resultb.append(row[12])
                    resultb.append(row[13])
                    resultb.append(row[14])
                    resultb.append(row[15])
                    resultb.append(row[16])
                    resultb.append(row[17])
                    resultb.append(row[18])
                    resultb.append(row[19])
                    resultb.append(row[20])
                    resultb.append(row[21])
                    resultb.append(row[22])
                    resultb.append(row[23])
                    resultb.append(row[24])
                    resultb.append(row[25])
                    db[row[0]] = resultb
                    f['B'] = db

                if row[1] == 3:  # C相用户
                    resultc.append(row[2])
                    resultc.append(row[3])
                    resultc.append(row[4])
                    resultc.append(row[5])
                    resultc.append(row[6])
                    resultc.append(row[7])
                    resultc.append(row[8])
                    resultc.append(row[9])
                    resultc.append(row[10])
                    resultc.append(row[11])
                    resultc.append(row[12])
                    resultc.append(row[13])
                    resultc.append(row[14])
                    resultc.append(row[15])
                    resultc.append(row[16])
                    resultc.append(row[17])
                    resultc.append(row[18])
                    resultc.append(row[19])
                    resultc.append(row[20])
                    resultc.append(row[21])
                    resultc.append(row[22])
                    resultc.append(row[23])
                    resultc.append(row[24])
                    resultc.append(row[25])
                    dc[row[0]] = resultc
                    f['C'] = dc
        except:
            print
            'MySQL connect fail...'
        else:
            # 使用json.dumps将数据转换为json格式，json.dumps方法默认会输出成这种格式"\u5377\u76ae\u6298\u6263"，加ensure_ascii=False，则能够防止中文乱码。
            # JSON采用完全独立于语言的文本格式，事实上大部分现代计算机语言都以某种形式支持它们。这使得一种数据格式在同样基于这些结构的编程语言之间交换成为可能。
            # json.dumps()是将原始数据转为json（其中单引号会变为双引号），而json.loads()是将json转为原始数据。
            # default=default 用来 TypeError: Decimal('0.23336') is not JSON serializable 问题   即json 无法序列化问题
            # indent=4 缩进 美观json 作用
            jsondatar = json.dumps(f, ensure_ascii=False, indent=4, default=self.default) #  default 参数作用 将数据转为json 时调用 解决序列化异常问题
            # 去除首尾的中括号
            return jsondatar
            # jsondatar[1:len(jsondatar) - 1]


    # 表格最终数据存入数据库
    def excel_to_sql(self, data_lists):
        for data_list in data_lists:
            # 区分台区用户和普通用户
            if(len(data_list)) > 32:
                del data_list[6]
                del data_list[6]
                sql = """insert into tg_data VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
                self.conn.execute(sql, data_list)
            else:
                del data_list[6]
                del data_list[6]
                sql = """insert into user_data VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
                self.conn.execute(sql, data_list)


    def pg_into_mysql_information(self):
        value_set = self.pg_conn.pg_conn()
        conn = pymysql.Connect(host='47.114.180.73', user='admin', passwd='^!@admin().aike', db='test_data', port=31905,
                               charset='utf8')
        cur = conn.cursor()
        sql_truncate='TRUNCATE TABLE pg_data'
        sql = 'insert into pg_data values(%s,%s,%s)'
        cur.execute(sql_truncate)
        cur.executemany(sql, value_set)
        cur.close()
        conn.commit()
        conn.close()

    def get_data_assemble(self, sql, condition_tuple=()):
        '''

        :param sql: string 格式 sql语句
        :param condition_tuple:  查询条件元组 默认是没有查询条件的 若需要 则将sql依赖数据元组传入即可
        :return: 返回查询结果
        '''
        # 执行sql命令
        if condition_tuple:                                         # sql 语句赋值查询
            res = self.conn.execute(sql, condition_tuple)
            # 返回的数据类型是元组类型，每个条数据元素为元组类型:(('第一条数据的字段1的值','第一条数据的字段2的值',...,'第一条数据的字段N的值'),(第二条数据),...,(第N条数据))
            data = res.fetchall()
        else:                                                       # sql语句不需要赋值
            res = self.conn.execute(sql)
            data = res.fetchall()

        return data
    def insert_data_assemble(self, sql):
        '''

        :param sql: string 格式 sql语句
        :param condition_tuple:  查询条件元组 默认是没有查询条件的 若需要 则将sql依赖数据元组传入即可
        :return: 返回查询结果
        '''

        self.conn.execute(sql)


